package com.Xingcang_Dianxue.WhutBank.Models;

import com.Xingcang_Dianxue.WhutBank.Utils.JdbcUtils;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import java.sql.Connection;
import java.sql.SQLException;


public class DatabaseDriver {
    // 用 apache-DBUtils工具类 执行sql语句
    static QueryRunner runner = new QueryRunner();
    // 用 druid连接池 连接数据库
    static Connection connection = JdbcUtils.getInstance().getConnection();


    // Client Section

    // 通过手机号和密码查询用户数据
    public Client getClientData(String phoneNumber, String password) {
        // 查询结果转换器，只要数据库字段和实体属性字母相同，可以忽略符号和大小写的差异
        BeanHandler<Client> bh = new BeanHandler<Client>(Client.class, new BasicRowProcessor(new GenerousBeanProcessor()));
        try {
            String sql = "SELECT * FROM clients WHERE PhoneNumber =? AND Password =?";
            return runner.query(connection, sql, bh, phoneNumber, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public Client getClient(String phoneNumber) {
        // 查询结果转换器，只要数据库字段和实体属性字母相同，可以忽略符号和大小写的差异
        BeanHandler<Client> bh = new BeanHandler<Client>(Client.class, new BasicRowProcessor(new GenerousBeanProcessor()));
        try {
            String sql = "SELECT * FROM clients WHERE PhoneNumber =? ";
            return runner.query(connection, sql, bh, phoneNumber);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    // 存款
    public void increaseBalance(String AccountType, String phoneNumber, double balance) {
        if (AccountType.equals("checking")) {
            try {
                String sql = "UPDATE checking_account SET Balance = Balance + ? WHERE Owner =?";
                runner.update(connection, sql, balance, phoneNumber);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } else {
            try {
                String sql = "UPDATE savings_account SET Balance = Balance + ? WHERE Owner =?";
                runner.update(connection, sql, balance, phoneNumber);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //取款
    public void decreaseBalance(String AccountType, String phoneNumber, double balance) {
        if (AccountType.equals("checking")) {
            try {
                String sql = "UPDATE checking_account SET Balance = Balance - ? WHERE Owner =?";
                runner.update(connection, sql, balance, phoneNumber);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } else {
            try {
                String sql = "UPDATE savings_account SET Balance = Balance - ? WHERE Owner =?";
                runner.update(connection, sql, balance, phoneNumber);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // 转账
    public void transfer(String AccountType, String phoneNumber, String targetPhoneNumber, double balance) {
        if (AccountType.equals("checking")) {
            try {
                String sql1 = "UPDATE checking_account SET Balance = Balance - ? WHERE Owner =?";
                runner.update(connection, sql1, balance, phoneNumber);
                String sql2 = "UPDATE checking_account SET Balance = Balance + ? WHERE Owner =?";
                runner.update(connection, sql2, balance, targetPhoneNumber);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } else {
            try {
                String sql1 = "UPDATE savings_account SET Balance = Balance - ? WHERE Owner =?";
                runner.update(connection, sql1, balance, phoneNumber);
                String sql2 = "UPDATE savings_account SET Balance = Balance + ? WHERE Owner =?";
                runner.update(connection, sql2, balance, targetPhoneNumber);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // 保存交易记录（存取款，转账）
    public void saveTransaction(String sender, String receiver, double amount, String date, String message, String type) {
        try {
            String sql = "INSERT INTO transactions(ID, Sender, Receiver, Amount, Date, Message, Type) VALUES(0,?,?,?,?,?,?) ";
            runner.update(connection, sql, sender, receiver, amount, date, message, type);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 查找交易记录（存取款，转账）
    public ObservableList<Transaction> getTransactions(String phoneNumber) {
        BeanListHandler<Transaction> bh = new BeanListHandler<>(Transaction.class, new BasicRowProcessor(new GenerousBeanProcessor()));
        try {
            String sql = "SELECT * FROM transactions WHERE Sender =? OR Receiver =?";
            return FXCollections.observableList(runner.query(connection, sql, bh, phoneNumber, phoneNumber));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }




    /*
     * Utility Methods Section
     */
    // 根据手机号码查询银行卡账户
    public CheckingAccount getCheckingAccountData(String Owner) {
        // 查询结果转换器，只要数据库字段和实体属性字母相同，可以忽略符号和大小写的差异
        BeanHandler<CheckingAccount> bh = new BeanHandler<CheckingAccount>(CheckingAccount.class, new BasicRowProcessor(new GenerousBeanProcessor()));
        try {
            String sql = "SELECT * FROM checking_account WHERE Owner =?";
            return runner.query(connection, sql, bh, Owner);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }



}
